﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class Request_BLL : IProject
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable GetDataProjectByYear(int Year, string HeaderID)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);   // prj
                dbManager.AddParameters(1, "@Year", Year);
                string sql = "select r.bgm_req_seq as Sequence, r.bgm_req_code as bgm_prj_code"
                                + ", r.bgm_req_name as bgm_prj_name, s.bgm_strat_list_name as Strategy"
                                + ", s.bgm_strat_list_name , j.job_site_name"
                            + " from bgm_request r"
                                + " left join bgm_strategy_data_list s on r.bgm_strat_list_seq = s.bgm_strat_list_seq"
                                + " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
                            + " where r.bgm_year = @Year and r.record_statusnot like@RecordStatus"
                            + " order by bgm_req_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //public DataTable GetDataProjectByYearJobSiteSourceStrategy(string bgm_year, string job_site_seq, string bgm_source_seq, string bgm_strat_list_seq)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(5);
        //        dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(1, "@bgm_year", bgm_year);
        //        dbManager.AddParameters(2, "@job_site_seq", job_site_seq);
        //        dbManager.AddParameters(3, "@bgm_source_seq", bgm_source_seq);
        //        dbManager.AddParameters(4, "@bgm_strat_list_seq", bgm_strat_list_seq);
        //        string sql = "select r.*, j.job_site_name, s.bgm_source_name, st.bgm_strat_list_name"
        //            + " from bgm_request r"
        //            + " left join bgm_strategy_data_list st on r.bgm_strat_list_seq = st.bgm_strat_list_seq"
        //            + " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
        //            + " left join bgm_mas_source s on r.bgm_source_seq = s.bgm_source_seq"
        //            + " left join (select re.bgm_req_seq"
        //                + ", sum(coalesce(re.total_amount, 0)) as total_amount"
        //                + " from bgm_request_expense re"
        //                + " group by re.bgm_req_seq"
        //                + ") re on r.bgm_req_seq = re.bgm_req_seq"
        //            + " where r.record_status not like @RecordStatus"
        //            + (!string.IsNullOrEmpty(bgm_year) ? " and r.bgm_year = @bgm_year" : "")
        //            + (!string.IsNullOrEmpty(job_site_seq) ? " and r.job_site_seq = @job_site_seq" : "")
        //            + (!string.IsNullOrEmpty(bgm_source_seq) ? " and r.bgm_source_seq = @bgm_source_seq" : "")
        //            + (!string.IsNullOrEmpty(bgm_strat_list_seq) ? " and r.bgm_strat_list_seq = @bgm_strat_list_seq" : "")
        //            + " order by r.bgm_req_name";

        //        DataTable dt = new DataTable();
        //        dbManager.Open();
        //        dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
        //        dbManager.Dispose();
        //        return dt;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}

        public DataTable ListProjectExpenseResource(string bgm_prj_seq, string bgm_exp_seq, string bgm_source_seq)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_req_seq", bgm_prj_seq);
                dbManager.AddParameters(2, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(3, "@bgm_source_seq", bgm_source_seq);

                string sql = " select e.*, i.* "
                           + " from bgm_request_expense e "
                           + " inner join icm_mas_resource_item i on e.icm_res_item_seq = i.icm_res_item_seq "
                           + " where e.icm_res_item_seq is not null and e.bgm_req_seq = @bgm_req_seq and bgm_exp_seq = @bgm_exp_seq and e.bgm_source_seq = @bgm_source_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListProjectExpenseGrid( string req_seq, string source, string exp_grp, bool show_all_exp)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@seq", req_seq);
                dbManager.AddParameters(2, "@source", source);
                dbManager.AddParameters(3, "@expgrp", exp_grp);

                string sql = " select e.bgm_exp_seq as value, e.bgm_exp_name as text"
                        //+ ", (select sum(rex.total_amount)"
                        //    + " from bgm_request_expense rex"
                        //    + " where rex.bgm_exp_seq = e.bgm_exp_seq"
                        //    + " and rex.bgm_req_seq = @seq"
                        //    + " and rex.bgm_source_seq = @source) as total_amount"
                        + ", re.total_amount"
                        + ", re.bgm_req_exp_seq as bgm_proj_exp_seq"
                        + ", eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name, eg.bgm_exp_grp_x"
                        + ", e1.bgm_exp_seq as bgm_exp_seq_1, e1.bgm_exp_name as bgm_exp_name_1"
                        + ", e2.bgm_exp_seq as bgm_exp_seq_2, e2.bgm_exp_name as bgm_exp_name_2"
                        + ", re.unit_price ,re.unit_qty ,re.descr"
                        + ", re.icm_res_item_seq"//, re.unit_price, re.unit_qty"
                        //+ ", e3.bgm_exp_seq as bgm_exp_seq_3, e3.bgm_exp_name as bgm_exp_name_3"
                    + " from bgm_mas_expense e"
                        + " left join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                        + " left join bgm_mas_expense e1 on e1.bgm_exp_seq = e.upper_bgm_exp_seq"
                        + " left join bgm_mas_expense e2 on e2.bgm_exp_seq = e1.upper_bgm_exp_seq"

                        //+ " left join bgm_mas_expense e3 on e3.bgm_exp_seq = e2.upper_bgm_exp_seq"
                        + " left join bgm_request_expense re on re.bgm_exp_seq = e.bgm_exp_seq"
                            + " and re.bgm_req_seq = @seq"
                            + " and re.bgm_source_seq = @source"

                        + (source.Length > 0 ? " inner join bgm_mas_expense_source es on e.bgm_exp_seq = es.bgm_exp_seq"
                        + " and es.bgm_source_seq = @source" : "")

                    + " where 0 = 0"
                    //+ " where e.record_status = @RecordStatus and e1.record_status = @RecordStatus and e2.record_status = @RecordStatus and eg.record_status = @RecordStatus"
                        + ((show_all_exp) ? "" : " and re.bgm_req_seq = @seq")
                        + ((exp_grp.Length > 0) ? " and e.bgm_exp_grp_seq = @expgrp" : "")
                        + " and e.bgm_exp_seq not in ( select upper_bgm_exp_seq from bgm_mas_expense where record_status = @RecordStatus and upper_bgm_exp_seq is not null)"
                    //+ " group by e.bgm_exp_seq, e.bgm_exp_name, re.bgm_req_exp_seq, eg.bgm_exp_grp_seq, eg.bgm_exp_grp_code, eg.bgm_exp_grp_name, eg.bgm_exp_grp_x"
                    //    + ", e1.bgm_exp_seq, e1.bgm_exp_name, e2.bgm_exp_seq, e2.bgm_exp_name"
                    //    + ", re.unit_price ,re.unit_qty ,re.descr, re.icm_res_item_seq"
                        //, e3.bgm_exp_seq, e3.bgm_exp_name"
                    + " order by eg.bgm_exp_grp_code, e.bgm_exp_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListProjectOptionGrid(string req_seq, string optionType)
        {
            try
            {
                DataTable dtb = GetDataProjectBySequence(req_seq);

                string strYear = dtb.Rows[0]["bgm_year"].ToString();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@seq", req_seq);
                dbManager.AddParameters(2, "@type", optionType);

                string sql = " select o.bgm_option_seq as value, o.bgm_option_name as text"
                            + ", o1.bgm_option_seq as bgm_option_seq_1, o1.bgm_option_code as bgm_option_code_1"
                            + ", o1.bgm_option_name as bgm_option_name_1"
                            + ", re.weight, re.bgm_req_opt_seq as bgm_proj_opt_seq"
                        + " from bgm_mas_option o"
                            + " left join bgm_mas_option o1 on o1.bgm_option_seq = o.upper_bgm_option_seq"
                            + " left join bgm_request_option re on re.bgm_option_seq = o.bgm_option_seq and re.bgm_req_seq = @seq"
                        + " where o.bgm_option_type_seq = @type"
                            + " and o.record_status not like @RecordStatus"
                            + " and o.bgm_option_seq not in ( select upper_bgm_option_seq from bgm_mas_option where record_status not like @RecordStatus and upper_bgm_option_seq is not null)"
                            + (optionType == "3" ? " and o.bgm_year = " + strYear : "")
                        + " order by o.bgm_option_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListProjectOptionRepeater(string req_seq, string upper_bgm_option_seq, string optionType)
        {
            try
            {
                DataTable dtb = GetDataProjectBySequence(req_seq);

                string strYear = dtb.Rows[0]["bgm_year"].ToString();

                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@seq", req_seq);
                dbManager.AddParameters(2, "@type", optionType);
                dbManager.AddParameters(3, "@upper_bgm_option_seq", upper_bgm_option_seq);
                dbManager.AddParameters(4, "@bgm_year", strYear);

                string sql = "select o.bgm_option_seq, o.bgm_option_name"
                    + ", o.upper_bgm_option_seq, coalesce(o1.child_count, 0) as child_count"
                    + ", re.weight, re.bgm_req_opt_seq as bgm_proj_opt_seq"
                    + " from bgm_mas_option o"
                    + " left join ( select upper_bgm_option_seq, count( * ) as child_count"
			            + " from bgm_mas_option"
			            + " where record_status not like @RecordStatus"
			            + " group by upper_bgm_option_seq"
			            + ") o1 on o.bgm_option_seq = o1.upper_bgm_option_seq"
                    + " left join bgm_request_option re on re.bgm_option_seq = o.bgm_option_seq" 
			            + " and re.bgm_req_seq = @seq"
                    + " where o.record_status not like @RecordStatus"
                    + (optionType != null ? " and o.bgm_option_type_seq = @type" : "")
                    + (optionType == "3" ? " and o.bgm_year = @bgm_year" : "")
                    + (upper_bgm_option_seq != null ? " and o.upper_bgm_option_seq = @upper_bgm_option_seq" : " and o.upper_bgm_option_seq is null")
                    + " order by o.bgm_option_code";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListProjectOptionTreeView(string req_seq, string optionType)
        {
            try
            {
                //DataTable dtb = GetDataProjectBySequence(req_seq);

                //string strYear = dtb.Rows[0]["bgm_year"].ToString();

                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@seq", req_seq);
                dbManager.AddParameters(2, "@type", optionType);
                //dbManager.AddParameters(3, "@bgm_year", strYear);

                string sql = "select o.bgm_option_seq, o.bgm_option_name, o.upper_bgm_option_seq, re.bgm_req_opt_seq as bgm_proj_opt_seq"
                    + " from bgm_mas_option o"
                    //+ " left join ( select upper_bgm_option_seq, count( * ) as child_count"
                    //    + " from bgm_mas_option"
                    //    + " where record_status not like @RecordStatus"
                    //    + " group by upper_bgm_option_seq"
                    //    + ") o1 on o.bgm_option_seq = o1.upper_bgm_option_seq"
                    + " left join bgm_request_option re on re.bgm_option_seq = o.bgm_option_seq"
                        + " and re.bgm_req_seq = @seq"
                    + " where o.record_status not like @RecordStatus"
                    + (optionType != null ? " and o.bgm_option_type_seq = @type" : "")
                    //+ (optionType == "3" ? " and o.bgm_year = @bgm_year" : "")
                    + " order by o.bgm_option_code";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectBySequence(string seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@seq", seq);    // prj
                string sql = "select  r.bgm_req_seq as Sequence"
                    + ", r.bgm_req_code as bgm_prj_code, r.bgm_req_name as bgm_prj_name"
                    + ", r.bgm_req_descr as bgm_prj_descr, r.bgm_req_manager as bgm_prj_manager, r.bgm_req_scope as bgm_prj_scope"
                    + ", r.bgm_req_date as bgm_prj_date, r.bgm_req_reason as bgm_prj_reason, r.bgm_req_object as bgm_prj_object"
                    + ", r.bgm_req_target as bgm_prj_target, r.bgm_req_plan as bgm_prj_plan, r.bgm_req_aim as bgm_prj_aim, r.bgm_req_profit as bgm_prj_profit, r.bgm_req_remark as bgm_prj_remark"
                    + ", s.bgm_strat_list_name, j.job_site_name, jm.job_site_name as job_site_name_main"
                    + ", r.bgm_year, r.bgm_strat_list_seq, r.job_site_seq, r.job_site_seq_main"
                    + ", s.bgm_strat_list_name, ms.bgm_scope_name"
                    + ", r.prj_type_seq, r.date_start, r.date_end, pt.prj_type_name, r.record_status"
                    + ", re.total_amount, null as gfmis_code"
                    + " from bgm_request r"
                    + " left join bgm_strategy_data_list s on r.bgm_strat_list_seq = s.bgm_strat_list_seq"
                    + " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
                    + " left join hrm_mp_job_sites jm on r.job_site_seq_main = jm.job_site_seq"
                    + " left join bgm_mas_scopes ms on r.bgm_req_scope = ms.bgm_scope_seq"
                    + " left join bgm_mas_project_type pt on pt.prj_type_seq = r.prj_type_seq"
                    + " left join (select bgm_req_seq"
                        + ", sum(coalesce(total_amount, 0)) as total_amount"
                        + " from bgm_request_expense"
                        + " group by bgm_req_seq"
                        + ") re on r.bgm_req_seq = re.bgm_req_seq"

                           + " where r.bgm_req_seq = @seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectBySequenceForGridIn(string seq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                string sql = "select distinct s.bgm_source_seq, s.bgm_source_name"
                    + ", 0 as approve_amount, coalesce(ae.total_amount, 0) as total_amount"
                    + " from  bgm_mas_source s"
                    + " left join bgm_mas_source s2 on s.bgm_source_seq = s2.upper_bgm_source_seq"
                        + " and s2.record_status = @RecordStatus"
                    + " left join (select bgm_req_seq, bgm_source_seq"
	                    + ", sum(coalesce(ae.total_amount, 0)) as total_amount"
	                    + " from bgm_request_expense ae"
	                    + " group by bgm_req_seq, bgm_source_seq"
	                    + ") ae on ae.bgm_source_seq = s.bgm_source_seq"
	                    + " and ae.bgm_req_seq = @seq"
                    + " left join bgm_request r on ae.bgm_req_seq = r.bgm_req_seq"
                    + " where s.record_status = @RecordStatus"
                    + " and s.use_request = '1'"
                    + " and (s2.bgm_source_seq is null"
                    + " or s2.use_request = '0'"
                    + ")";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectExpGrpBySequenceForGridIn(string seq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                string sql = "select s.bgm_exp_grp_seq, s.bgm_exp_grp_name"
                        + ", 0 as approve_amount"
                        + ", coalesce(sum(ae.total_amount), 0) as total_amount"
                           + " from bgm_mas_expense_group s"

                           + " left join (select ae.*, exp.bgm_exp_grp_seq"

                            + " from bgm_request_expense ae"
                            + " left join bgm_mas_expense exp on ae.bgm_exp_seq = exp.bgm_exp_seq"
                            + ") ae on ae.bgm_exp_grp_seq = s.bgm_exp_grp_seq and ae.bgm_req_seq = @seq"
                           + " left join bgm_request r on ae.bgm_req_seq = r.bgm_req_seq"
                           + " where s.record_status = @RecordStatus"
                           + " group by s.bgm_exp_grp_seq, s.bgm_exp_grp_name"
                           + " order by s.bgm_exp_grp_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public DataTable GetDataProjectBySequenceForGridIn(string seq)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(2);
        //        dbManager.AddParameters(0, "@seq", seq);
        //        dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ActiveRecordStatus);

        //        string sql = "select s.bgm_source_seq, s.bgm_source_name"
        //                + ", 0 as approve_amount"
        //                + ", coalesce(sum(ae.total_amount), 0) as total_amount"
        //                   + " from  bgm_mas_source s "
        //                   + " left join bgm_request_expense ae on ae.bgm_source_seq = s.bgm_source_seq and ae.bgm_req_seq = @seq"
        //                   + " left join bgm_request r on ae.bgm_req_seq = r.bgm_req_seq"
        //                   + " where s.record_status = @RecordStatus"
        //                   + " and s.bgm_source_seq not in (select upper_bgm_source_seq from bgm_mas_source where record_status = @RecordStatus and upper_bgm_source_seq is not null)"
        //                   + " group by s.bgm_source_seq, s.bgm_source_name"
        //                   + " order by s.bgm_source_code";

        //        DataTable dt = new DataTable();
        //        dbManager.Open();
        //        dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
        //        return dt;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}

        public DataTable GetDataProjectAndSource(string HeaderID, string StrategyID)
        {
            try
            {
                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public DataTable GetDataProjectAndSourceGroup(string HeaderID, string StrategyID)
        {
            try
            {
                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectOption(string bgm_req_seq, string bgm_option_type_seq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@seq", bgm_req_seq);
                dbManager.AddParameters(1, "@type", bgm_option_type_seq);

                string sql = "select ro.bgm_req_opt_seq as Sequence, o.bgm_option_name, ro.weight, ro.bgm_option_seq"
                           + ", (weight * (select coalesce(sum(total_amount), 0) from bgm_request_expense s where s.bgm_req_seq = ro.bgm_req_seq )) / 100 as total_amount, descr"
                           + " from bgm_request_option ro"
                           + " inner join bgm_mas_option o on ro.bgm_option_seq = o.bgm_option_seq"
                           + " where ro.bgm_req_seq = @seq"
                           + " and o.bgm_option_type_seq = @type";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectOptionBySequence(string seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select ro.bgm_req_opt_seq as Sequence, o.bgm_option_name, o.bgm_option_seq, ro.weight"
                           + ", (weight * (select coalesce(sum(total_amount), 0) from bgm_request_expense s where s.bgm_req_seq = ro.bgm_req_seq )) / 100 as total_amount, descr"
                           + " from bgm_request_option ro"
                           + " inner join bgm_mas_option o on ro.bgm_option_seq = o.bgm_option_seq"
                           + " where ro.bgm_req_opt_seq =" + seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectKPI(string bgm_req_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select rk.bgm_req_kpi_seq as Sequence, rk.bgm_req_kpi_name as bgm_prj_kpi_name"
                        + ", rk.unit, rk.bgm_req_kpi_weight as bgm_prj_kpi_weight"
                        + ", rk.target_m1, rk.target_m2, rk.target_m3, rk.target_m4"
                        + ", rk.target_m5, rk.target_m6, rk.target_m7, rk.target_m8"
                        + ", rk.target_m9, rk.target_m10, rk.target_m11, rk.target_m12"
                        + ", rk.target_m3 as target_q1, rk.target_m6 as target_q2"
                        + ", rk.target_m9 as target_q3, rk.target_m12 as target_q4"
                        + ", rk.target_m12 as target_year "
                        + ", kt.bgm_kpi_type_name"
                           + " from bgm_request_kpi rk"
                           + " inner join bgm_mas_kpi_type kt on rk.bgm_kpi_type_seq = kt.bgm_kpi_type_seq"
                           + " where rk.bgm_req_seq =" + bgm_req_seq;

                //SELECT * FROM branch INNER JOIN member ON ( branch.branch_id = member.branch_id)

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectKPIBySequence(string bgm_req_kpi_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select *, bgm_req_kpi_name as bgm_prj_kpi_name, bgm_req_kpi_descr as bgm_prj_kpi_descr"
                    + ", bgm_req_kpi_weight as bgm_prj_kpi_weight"
                    + ", target_m1 + target_m2 + target_m3 as target_q1"
                    + ", target_m4 + target_m5 + target_m6 as target_q2"
                    + ", target_m7 + target_m8 + target_m9 as target_q3"
                    + ", target_m10 + target_m11 + target_m12 as target_q4 "
                        + ", target_m1 + target_m2 + target_m3 "
                        + "+ target_m4 + target_m5 + target_m6 "
                        + "+ target_m7 + target_m8 + target_m9 "
                        + "+ target_m10 + target_m11 + target_m12 as target_year"
                    + " from bgm_request_kpi"
                    + " where bgm_req_kpi_seq =" + bgm_req_kpi_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectActivityPlan(string bgm_req_seq)
        {
            try
            {
                string sql = "select bgm_req_act_plan_seq as Sequence, descr" +
                             " , act_month1, act_month2, act_month3, act_month4"
                             + " , act_month5, act_month6, act_month7, act_month8" +
                             " , act_month9, act_month10, act_month11, act_month12"
                             + " from bgm_request_activity_plan" +
                             " where bgm_req_seq = '" + bgm_req_seq + "'";
  
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectActivityPlanBySequence(string bgm_req_act_plan_seq)
        {
            try
            {
                //string sql = "select ap.bgm_req_act_plan_seq as Sequence, ap.descr"
                //       + " , ap.act_month1, ap.act_month2, ap.act_month3, ap.act_month4"
                //       + " , ap.act_month5, ap.act_month6, ap.act_month7, ap.act_month8"
                //       + " , ap.act_month9, ap.act_month10, ap.act_month11, ap.act_month12"
                //       + " , apt.bgm_req_act_plan_target_seq, apt.target, apt.start_month, apt.end_month"
                //       + " from bgm_request_activity_plan ap left join bgm_request_activity_plan_target apt on ap.bgm_req_act_plan_seq = apt.bgm_req_act_plan_seq"
                //       + " where apt.bgm_req_act_plan_seq =" + bgm_req_act_plan_seq;

                string sql = "select ap.bgm_req_act_plan_seq as Sequence, ap.descr"
                       + " , ap.act_month1, ap.act_month2, ap.act_month3, ap.act_month4"
                       + " , ap.act_month5, ap.act_month6, ap.act_month7, ap.act_month8"
                       + " , ap.act_month9, ap.act_month10, ap.act_month11, ap.act_month12"
                       + " , (select count(*) from bgm_request_activity_plan_target as apt where apt.bgm_req_act_plan_seq =" + bgm_req_act_plan_seq + ") as rowTarget"
                       + " from bgm_request_activity_plan ap"
                       + " where ap.bgm_req_act_plan_seq =" + bgm_req_act_plan_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectActivityPlanTarget(string bgm_req_act_plan_seq)
        {
            try
            {
                string sql = "select bgm_req_act_plan_target_seq as Sequence, target, start_month, end_month"
                       + " from bgm_request_activity_plan_target"
                       + " where bgm_req_act_plan_seq =" + bgm_req_act_plan_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListPRojectExpense(string bgm_req_seq, string source)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select eg.bgm_exp_grp_name, e1.bgm_exp_name as name, r.bgm_req_seq, re.bgm_req_exp_seq as Sequence, re.bgm_source_seq"
                               + ", s.bgm_source_name, eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name, e1.bgm_exp_seq, e.bgm_exp_name, re.bgm_exp_seq, e.bgm_exp_name"
                               + ", re.icm_res_item_seq, i.icm_res_item_name, re.total_amount"
                           + " from bgm_mas_source s "
                               + " left join bgm_request_expense re.bgm_source_seq = s.bgm_source_seq and r.bgm_req_seq = " + bgm_req_seq
                               + " left join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                               + " left join bgm_mas_expense e1 on e1.bgm_exp_seq = e.upper_bgm_exp_seq and e1.upper_bgm_exp_seq is null"
                               + " left join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                               + " left join icm_mas_resource_item i on i.icm_res_item_seq = re.icm_res_item_seq"
                           + " where re.bgm_source_seq = " + source;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpense(string bgm_req_seq, string source, string bgm_exp_grp_seq)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@seq", bgm_req_seq);
                dbManager.AddParameters(1, "@source", source);
                dbManager.AddParameters(2, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                string sql = "select eg.bgm_exp_grp_name, e1.bgm_exp_name as name, r.bgm_req_seq, re.bgm_req_exp_seq as Sequence, re.bgm_source_seq"
                               + ", s.bgm_source_name, eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name, e1.bgm_exp_seq as upper_bgm_exp_seq, e.bgm_exp_name, re.bgm_exp_seq, e.bgm_exp_name"
                               + ", re.icm_res_item_seq, i.icm_res_item_name, re.total_amount"
                           + " from bgm_request r"
                               + " inner join bgm_request_expense re on re.bgm_req_seq = r.bgm_req_seq"
                               + " left join bgm_mas_source s on re.bgm_source_seq = s.bgm_source_seq"
                               + " left join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                               + " left join bgm_mas_expense e1 on e1.bgm_exp_seq = e.upper_bgm_exp_seq and e1.upper_bgm_exp_seq is null"
                               + " left join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                               + " left join icm_mas_resource_item i on i.icm_res_item_seq = re.icm_res_item_seq"
                           + " where r.bgm_req_seq = @seq"
                           + ((source.Length > 0) ? " and re.bgm_source_seq = @source" : "")
                           + ((bgm_exp_grp_seq.Length > 0) ? " and e.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "");

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpenseOnlyExp(string bgm_req_seq, string source, string bgm_exp_grp_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select r.bgm_req_seq, re.bgm_req_exp_seq, re.bgm_exp_seq"
                    + ", e.bgm_exp_name, re.total_amount"
                    + " from bgm_request r"
                    + " inner join bgm_request_expense re on re.bgm_req_seq = r.bgm_req_seq"
                    + " left join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                    + " where r.bgm_req_seq =" + bgm_req_seq
                    + ((source.Length > 0) ? " and re.bgm_source_seq = " + source : "")
                    + ((bgm_exp_grp_seq.Length > 0) ? " and e.bgm_exp_grp_seq = " + bgm_exp_grp_seq : "");

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectReportExpense(string bgm_req_seq, string source)
        {
            return null;
        }

        public DataTable GetDataProjectExpenseBySequence(string bgm_req_exp_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select s.bgm_source_name, eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name, e1.bgm_exp_name, e.bgm_exp_name, i.icm_res_item_name, re.*"
                           + " from bgm_request_expense re"
                           + " left join bgm_mas_source s on re.bgm_source_seq = s.bgm_source_seq"
                           + " left join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                           + " left join bgm_mas_expense e1 on e1.bgm_exp_seq = e.upper_bgm_exp_seq"
                           + " left join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                           + " left join icm_mas_resource_item i on i.icm_res_item_seq = re.icm_res_item_seq"

                           + " where re.bgm_req_exp_seq =" + bgm_req_exp_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpenseGroup(string bgm_req_seq, string bgm_source_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name, coalesce(sum(re.total_amount), 0) as total_amount"
                           + " from bgm_mas_expense_group eg"
                           //+ " left join bgm_mas_source s on eg.bgm_source_seq = s.bgm_source_seq"
                           + " left join bgm_mas_expense e on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                           + " left join bgm_request_expense re on re.bgm_exp_seq = e.bgm_exp_seq"
                           + " where eg.record_status = 'U'"
                           + " and re.bgm_req_seq =" + bgm_req_seq
                           + " and re.bgm_source_seq =" + bgm_source_seq
                           + " group by eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name, eg.bgm_exp_grp_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpensePlan(string bgm_req_seq, string bgm_source_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select rp.bgm_req_exp_plan_seq as Sequence, " +
                            @" 
CONCAT(
s.bgm_source_name ,'/',
eg.bgm_exp_grp_name, '/',
e3.bgm_exp_name, '/',
e2.bgm_exp_name,  '/',
e.bgm_exp_name) bgm_exp_name " +
                             ", re.bgm_req_exp_seq as bgm_prj_exp_seq, re.total_amount"
                                + " , rp.amount_month1, rp.amount_month2, rp.amount_month3, rp.amount_month4"
                                + " , rp.amount_month5, rp.amount_month6, rp.amount_month7, rp.amount_month8"
                                + " , rp.amount_month9, rp.amount_month10, rp.amount_month11, rp.amount_month12"
                                + " , eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name, s.bgm_source_seq, s.bgm_source_name"
                           + " from bgm_request r"
                                + " inner join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq"
                                + " inner join bgm_mas_source s on s.bgm_source_seq = re.bgm_source_seq"
                                + " inner join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                                + @"
INNER JOIN bgm_mas_expense e2 ON e.upper_bgm_exp_seq = e2. bgm_exp_seq
INNER JOIN bgm_mas_expense e3 ON e2.upper_bgm_exp_seq = e3.bgm_exp_seq 
"
                                + " inner join bgm_mas_expense_group eg on eg.bgm_exp_grp_seq = e.bgm_exp_grp_seq" 
                                + " left join bgm_request_expense_plan rp on rp.bgm_req_exp_seq = re.bgm_req_exp_seq"
                           + " where r.bgm_req_seq = " + bgm_req_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpensePlanBySequence(string bgm_req_exp_plan_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select ep.*,re.total_amount"
                           + " from bgm_request_expense_plan ep"
                           + " inner join bgm_request_expense re on re.bgm_req_exp_seq = ep.bgm_req_exp_seq"
                           + " where bgm_req_exp_plan_seq = " + bgm_req_exp_plan_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

//-- เพิ่งเพิ่มใหม่ ยังไม่ได้แก้ของเดิม เอาแค่ใช้ได้

        public int InsertProject(string bgm_year, DateTime bgm_req_date, string bgm_req_name
            , string bgm_strat_list_seq, string job_site_seq, string prj_type_seq
            , string bgm_req_scope, string bgm_req_reason, string bgm_req_object
            , string bgm_req_plan, string bgm_req_target, string bgm_req_aim, string bgm_req_profit, string bgm_req_remark
            , string date_start, string date_end, string CreatedBy, string job_site_seq_main)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(20);
                dbManager.AddParameters(0, "@bgm_year", bgm_year);
                dbManager.AddParameters(1, "@bgm_req_date", bgm_req_date);
                dbManager.AddParameters(2, "@bgm_req_name", bgm_req_name);
                dbManager.AddParameters(3, "@bgm_strat_list_seq", bgm_strat_list_seq);
                dbManager.AddParameters(4, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(5, "@prj_type_seq", prj_type_seq);
                dbManager.AddParameters(6, "@bgm_req_scope", bgm_req_scope);
                dbManager.AddParameters(7, "@bgm_req_reason", bgm_req_reason);
                dbManager.AddParameters(8, "@bgm_req_object", bgm_req_object);
                dbManager.AddParameters(9, "@bgm_req_plan", bgm_req_plan);
                dbManager.AddParameters(10, "@bgm_req_target", bgm_req_target);
                dbManager.AddParameters(11, "@bgm_req_remark", bgm_req_remark);

                dbManager.AddParameters(12, "@date_start", Convert.ToDateTime(date_start));
                dbManager.AddParameters(13, "@date_end", Convert.ToDateTime(date_end));

                dbManager.AddParameters(14, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(15, "@created_by", CreatedBy);
                dbManager.AddParameters(16, "@created_when", DateTime.Now);
                dbManager.AddParameters(17, "@job_site_seq_main", job_site_seq_main);

                dbManager.AddParameters(18, "@bgm_req_aim", bgm_req_aim);
                dbManager.AddParameters(19, "@bgm_req_profit", bgm_req_profit);

                string strsql = "Insert into bgm_request (bgm_year, bgm_req_date, bgm_req_name, bgm_strat_list_seq, job_site_seq"
                              + ", prj_type_seq, bgm_req_scope, bgm_req_reason, bgm_req_object, bgm_req_plan, bgm_req_target, bgm_req_aim, bgm_req_profit"
                              + ", bgm_req_remark, date_start, date_end, record_status, created_by, created_when, job_site_seq_main)"
                              + " values(@bgm_year, @bgm_req_date, @bgm_req_name, @bgm_strat_list_seq, @job_site_seq"
                              + ", @prj_type_seq, @bgm_req_scope, @bgm_req_reason, @bgm_req_object, @bgm_req_plan, @bgm_req_target, @bgm_req_aim, @bgm_req_profit"
                              + ", @bgm_req_remark, @date_start, @date_end, @record_status, @created_by, @created_when, @job_site_seq_main);"
                              + " select scope_identity()";
                string strsqlLast_insert = dbManager.ExecuteDataTable(CommandType.Text, strsql).Rows[0][0].ToString();

                dbManager.CommitTransaction();
                return Convert.ToInt32(strsqlLast_insert);
            }
            catch (Exception e)
            {
                dbManager.RollBackTransaction();
                return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int InsertProject(string header_seq, string bgm_year, DateTime bgm_req_date, string bgm_strat_list_seq
            , string job_site_seq, string prj_type_seq, string bgm_req_manager, string bgm_req_scope, string bgm_req_code
            , string bgm_req_name, string bgm_req_descr, string bgm_req_reason, string bgm_req_object, string bgm_req_plan
            , string bgm_req_target, string bgm_req_remark, string CreatedBy, string job_site_seq_main)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(19);
                dbManager.AddParameters(0, "@bgm_year", bgm_year);
                dbManager.AddParameters(1, "@bgm_req_date", bgm_req_date); //bgm_req_date
                dbManager.AddParameters(2, "@bgm_strat_list_seq", bgm_strat_list_seq);
                dbManager.AddParameters(3, "@job_site_seq", job_site_seq);

                dbManager.AddParameters(4, "@prj_type_seq", prj_type_seq);
                dbManager.AddParameters(5, "@bgm_req_manager", bgm_req_manager);
                dbManager.AddParameters(6, "@bgm_req_scope", bgm_req_scope);
                dbManager.AddParameters(7, "@bgm_req_code", bgm_req_code);

                dbManager.AddParameters(8, "@bgm_req_name", bgm_req_name);
                dbManager.AddParameters(9, "@bgm_req_descr", bgm_req_descr);
                dbManager.AddParameters(10, "@bgm_req_reason", bgm_req_reason);
                dbManager.AddParameters(11, "@bgm_req_object", bgm_req_object);

                dbManager.AddParameters(12, "@bgm_req_plan", bgm_req_plan);
                dbManager.AddParameters(13, "@bgm_req_target", bgm_req_target);
                dbManager.AddParameters(14, "@bgm_req_remark", bgm_req_remark);
                dbManager.AddParameters(15, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(16, "@created_by", CreatedBy);
                dbManager.AddParameters(17, "@created_when", DateTime.Now);
                dbManager.AddParameters(18, "@job_site_seq_main", job_site_seq_main);
                string strsql = "Insert into bgm_request ("
                        + "bgm_year,"
                        + "bgm_req_date,"
                        + "bgm_strat_list_seq,"
                        + "job_site_seq,"
                        + "prj_type_seq,"
                        + "bgm_req_scope,"
                        + "bgm_req_code,"
                        + "bgm_req_name,"
                        + "bgm_req_descr,"
                        + "bgm_req_reason,"
                        + "bgm_req_object,"
                        + "bgm_req_plan,"
                        + "bgm_req_target,"
                        + "bgm_req_remark,"
                        + "record_status,"
                        + "created_by,"
                        + "created_when,"
                        + "job_site_seq_main)"
                    + " values("
                        + "@bgm_year,"
                        + "@bgm_req_date,"
                        + "@bgm_strat_list_seq,"
                        + "@job_site_seq,"
                        + "@prj_type_seq,"
                        + "@bgm_req_scope,"
                        + "@bgm_req_code,"
                        + "@bgm_req_name,"
                        + "@bgm_req_descr,"
                        + "@bgm_req_reason,"
                        + "@bgm_req_object,"
                        + "@bgm_req_plan,"
                        + "@bgm_req_target,"
                        + "@bgm_req_remark,"
                        + "@record_status,"
                        + "@created_by,"
                        + "@created_when,"
                        + "@job_site_seq_main);"
                        + " select scope_identity()";
                string strsqlLast_insert = dbManager.ExecuteDataTable(CommandType.Text, strsql).Rows[0][0].ToString();
                return Convert.ToInt32(strsqlLast_insert);
            }
            catch
            {
                return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectCustoms(string bgm_req_seq, string prj_ctrl_seq, string descr)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@bgm_req_seq", bgm_req_seq);
                dbManager.AddParameters(1, "@prj_ctrl_seq", prj_ctrl_seq);
                dbManager.AddParameters(2, "@descr", descr);
                string strsql = "Insert into bgm_request_customs_field ("
                                            + "bgm_req_seq,"
                                            + "prj_ctrl_seq,"
                                            + "descr)"
                                            + " values("
                                            + "@bgm_req_seq,"
                                            + "@prj_ctrl_seq,"
                                            + "@descr)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectOption(string bgm_req_seq, string descr, Nullable<Double> weight, string bgm_option_seq, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@bgm_req_seq", bgm_req_seq);
                dbManager.AddParameters(1, "@descr", descr); //bgm_req_date
                dbManager.AddParameters(2, "@weight", weight);
                dbManager.AddParameters(3, "@bgm_option_seq", bgm_option_seq);

                dbManager.AddParameters(4, "@created_by", created_by);
                dbManager.AddParameters(5, "@created_when", DateTime.Now);
                string strsql = "Insert into bgm_request_option ("
                                            + "bgm_req_seq,"
                                            + "descr,"
                                            + "weight,"
                                            + "bgm_option_seq,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_req_seq,"
                                            + "@descr,"
                                            + "@weight,"
                                            + "@bgm_option_seq,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }   
            catch
            {
               // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectKPI(string bgm_req_kpi_name, string bgm_req_kpi_descr, string unit, string bgm_req_kpi_weight, string bgm_req_seq, string bgm_kpi_type_seq, string score_1, string score_2, string score_3, string score_4, string score_5, string target_type
                , string target_m1, string target_m2, string target_m3, string target_m4, string target_m5, string target_m6, string target_m7, string target_m8, string target_m9, string target_m10, string target_m11, string target_m12
                , string mtef_1, string mtef_2, string mtef_3, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(29);
                dbManager.AddParameters(0, "@bgm_req_kpi_name", bgm_req_kpi_name);
                dbManager.AddParameters(1, "@bgm_req_kpi_descr", bgm_req_kpi_descr);
                dbManager.AddParameters(2, "@unit", unit);
                dbManager.AddParameters(3, "@bgm_req_kpi_weight", bgm_req_kpi_weight);
                dbManager.AddParameters(4, "@bgm_req_seq", bgm_req_seq);

                dbManager.AddParameters(5, "@bgm_kpi_type_seq", bgm_kpi_type_seq);
                dbManager.AddParameters(6, "@score_1", string.IsNullOrEmpty(score_1) ? "0" : score_1);
                dbManager.AddParameters(7, "@score_2", string.IsNullOrEmpty(score_2) ? "0" : score_2);
                dbManager.AddParameters(8, "@score_3", string.IsNullOrEmpty(score_3) ? "0" : score_3);
                dbManager.AddParameters(9, "@score_4", string.IsNullOrEmpty(score_4) ? "0" : score_4);
                dbManager.AddParameters(10, "@score_5", string.IsNullOrEmpty(score_5) ? "0" : score_5);

                dbManager.AddParameters(11, "@target_type", target_type);
                dbManager.AddParameters(12, "@target_m1", string.IsNullOrEmpty(target_m1) ? "0" : target_m1);
                dbManager.AddParameters(13, "@target_m2", string.IsNullOrEmpty(target_m2) ? "0" : target_m2);
                dbManager.AddParameters(14, "@target_m3", string.IsNullOrEmpty(target_m3) ? "0" : target_m3);
                dbManager.AddParameters(15, "@target_m4", string.IsNullOrEmpty(target_m4) ? "0" : target_m4);
                dbManager.AddParameters(16, "@target_m5", string.IsNullOrEmpty(target_m5) ? "0" : target_m5);
                dbManager.AddParameters(17, "@target_m6", string.IsNullOrEmpty(target_m6) ? "0" : target_m6);
                dbManager.AddParameters(18, "@target_m7", string.IsNullOrEmpty(target_m7) ? "0" : target_m7);
                dbManager.AddParameters(19, "@target_m8", string.IsNullOrEmpty(target_m8) ? "0" : target_m8);
                dbManager.AddParameters(20, "@target_m9", string.IsNullOrEmpty(target_m9) ? "0" : target_m9);
                dbManager.AddParameters(21, "@target_m10", string.IsNullOrEmpty(target_m10) ? "0" : target_m10);
                dbManager.AddParameters(22, "@target_m11", string.IsNullOrEmpty(target_m11) ? "0" : target_m11);
                dbManager.AddParameters(23, "@target_m12", string.IsNullOrEmpty(target_m12) ? "0" : target_m12);
                dbManager.AddParameters(24, "@mtef_1", string.IsNullOrEmpty(mtef_1) ? "0" : mtef_1);
                dbManager.AddParameters(25, "@mtef_2", string.IsNullOrEmpty(mtef_2) ? "0" : mtef_2);
                dbManager.AddParameters(26, "@mtef_3", string.IsNullOrEmpty(mtef_3) ? "0" : mtef_3);
                dbManager.AddParameters(27, "@created_by", created_by);
                dbManager.AddParameters(28, "@created_when", DateTime.Now);
                string strsql = "Insert into bgm_request_kpi ("
                                             + "bgm_req_kpi_name,"
                                             + "bgm_req_kpi_descr,"
                                             + "unit,"
                                             + "bgm_req_kpi_weight,"
                                             + "bgm_req_seq,"
                                             + "bgm_kpi_type_seq,"
                                             + "score_1,"
                                             + "score_2,"
                                             + "score_3,"
                                             + "score_4,"
                                             + "score_5,"
                                             + "target_type,"
                                             + "target_m1,"
                                             + "target_m2,"
                                             + "target_m3,"
                                             + "target_m4,"
                                             + "target_m5,"
                                             + "target_m6,"
                                             + "target_m7,"
                                             + "target_m8,"
                                             + "target_m9,"
                                             + "target_m10,"
                                             + "target_m11,"
                                             + "target_m12,"
                                             + "mtef_1,"
                                             + "mtef_2,"
                                             + "mtef_3,"
                                             + "created_by,"
                                             + "created_when)"
                                             + " values("
                                             + "@bgm_req_kpi_name,"
                                             + "@bgm_req_kpi_descr,"
                                             + "@unit,"
                                             + "@bgm_req_kpi_weight,"
                                             + "@bgm_req_seq,"
                                             + "@bgm_kpi_type_seq,"
                                             + "@score_1,"
                                             + "@score_2,"
                                             + "@score_3,"
                                             + "@score_4,"
                                             + "@score_5,"
                                             + "@target_type,"
                                             + "@target_m1,"
                                             + "@target_m2,"
                                             + "@target_m3,"
                                             + "@target_m4,"
                                             + "@target_m5,"
                                             + "@target_m6,"
                                             + "@target_m7,"
                                             + "@target_m8,"
                                             + "@target_m9,"
                                             + "@target_m10,"
                                             + "@target_m11,"
                                             + "@target_m12,"
                                             + "@mtef_1,"
                                             + "@mtef_2,"
                                             + "@mtef_3,"
                                             + "@created_by,"
                                             + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int InsertProjectActivityPlan(string bgm_req_seq, string descr, int act_month1, int act_month2, int act_month3, int act_month4, int act_month5, int act_month6, int act_month7, int act_month8, int act_month9, int act_month10, int act_month11, int act_month12, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@bgm_req_seq", bgm_req_seq);
                dbManager.AddParameters(1, "@descr", descr); //bgm_req_date
                dbManager.AddParameters(2, "@act_month1", act_month1);
                dbManager.AddParameters(3, "@act_month2", act_month2);

                dbManager.AddParameters(4, "@act_month3", act_month3);
                dbManager.AddParameters(5, "@act_month4", act_month4);
                dbManager.AddParameters(6, "@act_month5", act_month5);
                dbManager.AddParameters(7, "@act_month6", act_month6);
                dbManager.AddParameters(8, "@act_month7", act_month7);
                dbManager.AddParameters(9, "@act_month8", act_month8);
                dbManager.AddParameters(10, "@act_month9", act_month9);
                dbManager.AddParameters(11, "@act_month10", act_month10);
                dbManager.AddParameters(12, "@act_month11", act_month11);
                dbManager.AddParameters(13, "@act_month12", act_month12);
                dbManager.AddParameters(14, "@created_by", created_by);
                dbManager.AddParameters(15, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_request_activity_plan ("
                                            + "bgm_req_seq,"
                                            + "descr,"
                                            + "act_month1,"
                                            + "act_month2,"
                                            + "act_month3,"
                                            + "act_month4,"
                                            + "act_month5,"
                                            + "act_month6,"
                                            + "act_month7,"
                                            + "act_month8,"
                                            + "act_month9,"
                                            + "act_month10,"
                                            + "act_month11,"
                                            + "act_month12,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_req_seq,"
                                            + "@descr,"
                                            + "@act_month1,"
                                            + "@act_month2,"
                                            + "@act_month3,"
                                            + "@act_month4,"
                                            + "@act_month5,"
                                            + "@act_month6,"
                                            + "@act_month7,"
                                            + "@act_month8,"
                                            + "@act_month9,"
                                            + "@act_month10,"
                                            + "@act_month11,"
                                            + "@act_month12,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                string strsqlLast_insert = "select distinct scope_identity() FROM bgm_request_activity_plan";
                DataSet ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strsqlLast_insert);
                dbManager.CommitTransaction();
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            catch
            {
                 return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectActivityPlanTarget(string bgm_req_act_plan_seq, string target, string start_month, string end_month, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@bgm_req_act_plan_seq", bgm_req_act_plan_seq);
                dbManager.AddParameters(1, "@target", target); //bgm_req_date
                dbManager.AddParameters(2, "@start_month", start_month);
                dbManager.AddParameters(3, "@end_month", end_month);

                dbManager.AddParameters(4, "@created_by", created_by);
                dbManager.AddParameters(5, "@created_when", DateTime.Now);
                string strsql = "Insert into bgm_request_activity_plan_target ("
                                            + "bgm_req_act_plan_seq,"
                                            + "target,"
                                            + "start_month,"
                                            + "end_month,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_req_act_plan_seq,"
                                            + "@target,"
                                            + "@start_month,"
                                            + "@end_month,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectExpense(string bgm_req_seq, string bgm_source_seq, string bgm_exp_seq, string total_amount, string descr, Nullable<int> icm_res_item_seq, Nullable<int> unit_qty, Nullable<decimal> unit_price, Nullable<decimal> mtef1, Nullable<decimal> mtef2, Nullable<decimal> mtef3, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);
                dbManager.AddParameters(0, "@bgm_req_seq", bgm_req_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(3, "@total_amount", total_amount);
                dbManager.AddParameters(4, "@descr", descr);

                dbManager.AddParameters(5, "@icm_res_item_seq", icm_res_item_seq);
                dbManager.AddParameters(6, "@unit_qty", unit_qty);
                dbManager.AddParameters(7, "@unit_price", unit_price);
                dbManager.AddParameters(8, "@mtef1", mtef1);
                dbManager.AddParameters(9, "@mtef2", mtef2);
                dbManager.AddParameters(10, "@mtef3", mtef3);
                dbManager.AddParameters(11, "@created_by", created_by);
                dbManager.AddParameters(12, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_request_expense ("
                                            + "bgm_req_seq,"
                                            + "bgm_source_seq,"
                                            + "bgm_exp_seq,"
                                            + "total_amount,"
                                            + "descr,"
                                            + "icm_res_item_seq,"
                                            + "unit_qty,"
                                            + "unit_price,"
                                            + "mtef1,"
                                            + "mtef2,"
                                            + "mtef3,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_req_seq,"
                                            + "@bgm_source_seq,"
                                            + "@bgm_exp_seq,"
                                            + "@total_amount,"
                                            + "@descr,"
                                            + "@icm_res_item_seq,"
                                            + "@unit_qty,"
                                            + "@unit_price,"
                                            + "@mtef1,"
                                            + "@mtef2,"
                                            + "@mtef3,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                //return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectExpensePlan(string bgm_req_exp_seq, string amount_month1, string amount_month2, string amount_month3, string amount_month4, string amount_month5, string amount_month6, string amount_month7, string amount_month8, string amount_month9, string amount_month10, string amount_month11, string amount_month12, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);
                dbManager.AddParameters(0, "@bgm_req_exp_seq", bgm_req_exp_seq);
                dbManager.AddParameters(1, "@amount_month1", amount_month1 != "" ? amount_month1 : "0" );
                dbManager.AddParameters(2, "@amount_month2", amount_month2 != "" ? amount_month2 : "0");
                dbManager.AddParameters(3, "@amount_month3", amount_month3 != "" ? amount_month3 : "0");
                dbManager.AddParameters(4, "@amount_month4", amount_month4 != "" ? amount_month4 : "0");
                dbManager.AddParameters(5, "@amount_month5", amount_month5 != "" ? amount_month5 : "0");
                dbManager.AddParameters(6, "@amount_month6", amount_month6 != "" ? amount_month6 : "0");
                dbManager.AddParameters(7, "@amount_month7", amount_month7 != "" ? amount_month7 : "0");
                dbManager.AddParameters(8, "@amount_month8", amount_month8 != "" ? amount_month8 : "0");
                dbManager.AddParameters(9, "@amount_month9", amount_month9 != "" ? amount_month9 : "0");
                dbManager.AddParameters(10, "@amount_month10", amount_month10 != "" ? amount_month10 : "0");
                dbManager.AddParameters(11, "@amount_month11", amount_month11 != "" ? amount_month11 : "0");
                dbManager.AddParameters(12, "@amount_month12", amount_month12 != "" ? amount_month12 : "0");
                dbManager.AddParameters(13, "@created_by", created_by);
                dbManager.AddParameters(14, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_request_expense_plan ("
                                            + "bgm_req_exp_seq,"
                                            + "amount_month1,"
                                            + "amount_month2,"
                                            + "amount_month3,"
                                            + "amount_month4,"
                                            + "amount_month5,"
                                            + "amount_month6,"
                                            + "amount_month7,"
                                            + "amount_month8,"
                                            + "amount_month9,"
                                            + "amount_month10,"
                                            + "amount_month11,"
                                            + "amount_month12,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_req_exp_seq,"
                                            + "@amount_month1,"
                                            + "@amount_month2,"
                                            + "@amount_month3,"
                                            + "@amount_month4,"
                                            + "@amount_month5,"
                                            + "@amount_month6,"
                                            + "@amount_month7,"
                                            + "@amount_month8,"
                                            + "@amount_month9,"
                                            + "@amount_month10,"
                                            + "@amount_month11,"
                                            + "@amount_month12,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                //return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public void UpdateProject(string Sequence, string bgm_year, DateTime bgm_req_date, string bgm_req_name
        //    , string bgm_strat_list_seq, string job_site_seq, string prj_type_seq, string bgm_req_scope
        //    , string bgm_req_reason, string bgm_req_object, string bgm_req_plan, string bgm_req_target
        //    , string bgm_req_remark, string date_start, string date_end, string UpdatedBy, string job_site_seq_main)
        //{
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.BeginTransaction();
        //        dbManager.CreateParameters(17);
        //        dbManager.AddParameters(0, "@seq", Sequence);
        //        dbManager.AddParameters(1, "@bgm_year", bgm_year);
        //        dbManager.AddParameters(2, "@bgm_req_name", bgm_req_name);
        //        dbManager.AddParameters(3, "@bgm_strat_list_seq", bgm_strat_list_seq);
        //        dbManager.AddParameters(4, "@job_site_seq", job_site_seq);
        //        dbManager.AddParameters(5, "@prj_type_seq", prj_type_seq);
        //        dbManager.AddParameters(6, "@bgm_req_scope", bgm_req_scope);
        //        dbManager.AddParameters(7, "@bgm_req_reason", bgm_req_reason);
        //        dbManager.AddParameters(8, "@bgm_req_object", bgm_req_object);
        //        dbManager.AddParameters(9, "@bgm_req_plan", bgm_req_plan);
        //        dbManager.AddParameters(10, "@bgm_req_target", bgm_req_target);
        //        dbManager.AddParameters(11, "@bgm_req_remark", bgm_req_remark);
        //        dbManager.AddParameters(12, "@date_start", Convert.ToDateTime(date_start));
        //        dbManager.AddParameters(13, "@date_end", Convert.ToDateTime(date_end));
        //        dbManager.AddParameters(14, "@updated_by", UpdatedBy);
        //        dbManager.AddParameters(15, "@updated_when", DateTime.Now);
        //        dbManager.AddParameters(16, "@job_site_seq_main", job_site_seq_main);

        //        string strsql = "UPDATE bgm_request"
        //            + " SET bgm_year = @bgm_year"
        //                + ", bgm_req_name = @bgm_req_name"
        //                + ", bgm_strat_list_seq = @bgm_strat_list_seq"
        //                + ", job_site_seq = @job_site_seq"
        //                + ", job_site_seq_main = @job_site_seq_main"
        //                + ", prj_type_seq = @prj_type_seq"
        //                + ", bgm_req_scope = @bgm_req_scope"
        //                + ", bgm_req_reason = @bgm_req_reason"
        //                + ", bgm_req_object = @bgm_req_object"
        //                + ", bgm_req_plan = @bgm_req_plan"
        //                + ", bgm_req_target = @bgm_req_target"
        //                + ", bgm_req_remark = @bgm_req_remark"
        //                + ", date_start = @date_start"
        //                + ", date_end = @date_end"
        //                + ", updated_by = @updated_by"
        //                + ", updated_when = @updated_when"
        //            + " WHERE bgm_req_seq = @seq";

        //        dbManager.ExecuteNonQuery(CommandType.Text, strsql);
        //        dbManager.CommitTransaction();
        //    }
        //    catch (Exception e)
        //    {
        //        dbManager.RollBackTransaction();
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}

        public void UpdateProject(string bgm_req_seq, DateTime bgm_req_date, string bgm_req_code, string bgm_req_name
            , string bgm_req_descr, string job_site_seq_main, string bgm_req_manager, string bgm_req_scope
            , string bgm_req_reason, string bgm_req_object, string bgm_req_target
            , string bgm_req_plan, string bgm_req_aim, string bgm_req_profit
            , string bgm_req_remark, string bgm_strat_list_seq, string prj_type_seq
            , DateTime date_start, DateTime date_end, string gfmis_code, string UpdatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(23);
                dbManager.AddParameters(0, "@seq", bgm_req_seq);
                dbManager.AddParameters(1, "@bgm_req_code", bgm_req_code);
                dbManager.AddParameters(2, "@bgm_req_name", bgm_req_name);
                dbManager.AddParameters(3, "@bgm_req_descr", bgm_req_descr);
                dbManager.AddParameters(4, "@bgm_req_manager", bgm_req_manager);
                dbManager.AddParameters(5, "@bgm_req_scope", bgm_req_scope);
                dbManager.AddParameters(6, "@bgm_req_date", bgm_req_date);
                dbManager.AddParameters(7, "@bgm_req_reason", bgm_req_reason);
                dbManager.AddParameters(8, "@bgm_req_object", bgm_req_object);
                dbManager.AddParameters(9, "@bgm_req_target", bgm_req_target);
                dbManager.AddParameters(10, "@bgm_req_plan", bgm_req_plan);
                dbManager.AddParameters(11, "@bgm_req_remark", bgm_req_remark);
                dbManager.AddParameters(12, "@bgm_strat_list_seq", bgm_strat_list_seq);
                dbManager.AddParameters(13, "@prj_type_seq", prj_type_seq);
                dbManager.AddParameters(14, "@job_site_seq", "");
                dbManager.AddParameters(15, "@bgm_year", "");
                dbManager.AddParameters(16, "@date_start", date_start);
                dbManager.AddParameters(17, "@date_end", date_end);
                dbManager.AddParameters(18, "@job_site_seq_main", job_site_seq_main);

                dbManager.AddParameters(19, "@updated_by", UpdatedBy);
                dbManager.AddParameters(20, "@updated_when", DateTime.Now);

                dbManager.AddParameters(21, "@bgm_req_aim", bgm_req_aim);
                dbManager.AddParameters(22, "@bgm_req_profit", bgm_req_profit);

                string strsql = "UPDATE bgm_request"
                    + " SET bgm_req_code = @bgm_req_code"
                        + ", bgm_req_name = @bgm_req_name"
                        + ", bgm_req_descr = @bgm_req_descr"
                        + ", bgm_req_manager = @bgm_req_manager"
                        + ", bgm_req_scope = @bgm_req_scope"
                        + ", bgm_req_date = @bgm_req_date"
                        + ", bgm_req_reason = @bgm_req_reason"
                        + ", bgm_req_object = @bgm_req_object"
                        + ", bgm_req_target = @bgm_req_target"
                        + ", bgm_req_plan = @bgm_req_plan"
                        + ", bgm_req_aim = @bgm_req_aim"
                        + ", bgm_req_profit = @bgm_req_profit"
                        + ", bgm_req_remark = @bgm_req_remark"
                        + ", job_site_seq_main = @job_site_seq_main"
                        + ", bgm_strat_list_seq = @bgm_strat_list_seq"
                        + ", prj_type_seq = @prj_type_seq"
                        + ", date_start = @date_start"
                        + ", date_end = @date_end"
                        //+ ", gfmis_code = @gfmis_code"
                        + ", updated_by = @updated_by"
                        + ", updated_when = @updated_when"
                    + " WHERE bgm_req_seq = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                throw ex;
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectOption(string Sequence, string descr, Nullable<Double> weight, string bgm_option_seq, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@bgm_option_seq", bgm_option_seq);
                dbManager.AddParameters(2, "@descr", descr);
                dbManager.AddParameters(3, "@weight", weight);
                dbManager.AddParameters(4, "@updated_by", UpdateBy);
                dbManager.AddParameters(5, "@updated_when", DateTime.Now);


                string strsql = "UPDATE bgm_request_option SET descr=@descr, bgm_option_seq=@bgm_option_seq, weight=@weight, updated_by=@updated_by, updated_when=@updated_when WHERE bgm_req_opt_seq=@sequence";



                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectKPI(string Sequence, string bgm_req_kpi_name, string bgm_req_kpi_descr, string unit, string bgm_req_kpi_weight, string bgm_kpi_type_seq, string score_1, string score_2, string score_3, string score_4, string score_5, string target_type
            , string target_m1, string target_m2, string target_m3, string target_m4, string target_m5, string target_m6, string target_m7, string target_m8, string target_m9, string target_m10, string target_m11, string target_m12
            , string mtef_1, string mtef_2, string mtef_3, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(29);
                dbManager.AddParameters(0, "@bgm_req_kpi_name", bgm_req_kpi_name);
                dbManager.AddParameters(1, "@bgm_req_kpi_descr", bgm_req_kpi_descr);
                dbManager.AddParameters(2, "@unit", unit);
                dbManager.AddParameters(3, "@bgm_req_kpi_weight", bgm_req_kpi_weight);

                dbManager.AddParameters(4, "@bgm_kpi_type_seq", bgm_kpi_type_seq);
                dbManager.AddParameters(5, "@score_1", string.IsNullOrEmpty(score_1) ? "0" : score_1);
                dbManager.AddParameters(6, "@score_2", string.IsNullOrEmpty(score_2) ? "0" : score_2);
                dbManager.AddParameters(7, "@score_3", string.IsNullOrEmpty(score_3) ? "0" : score_3);
                dbManager.AddParameters(8, "@score_4", string.IsNullOrEmpty(score_4) ? "0" : score_4);
                dbManager.AddParameters(9, "@score_5", string.IsNullOrEmpty(score_5) ? "0" : score_5);

                dbManager.AddParameters(10, "@target_type", target_type);
                dbManager.AddParameters(11, "@target_m1", string.IsNullOrEmpty(target_m1) ? "0" : target_m1);
                dbManager.AddParameters(12, "@target_m2", string.IsNullOrEmpty(target_m2) ? "0" : target_m2);
                dbManager.AddParameters(13, "@target_m3", string.IsNullOrEmpty(target_m3) ? "0" : target_m3);
                dbManager.AddParameters(14, "@target_m4", string.IsNullOrEmpty(target_m4) ? "0" : target_m4);
                dbManager.AddParameters(15, "@target_m5", string.IsNullOrEmpty(target_m5) ? "0" : target_m5);
                dbManager.AddParameters(16, "@target_m6", string.IsNullOrEmpty(target_m6) ? "0" : target_m6);
                dbManager.AddParameters(17, "@target_m7", string.IsNullOrEmpty(target_m7) ? "0" : target_m7);
                dbManager.AddParameters(18, "@target_m8", string.IsNullOrEmpty(target_m8) ? "0" : target_m8);
                dbManager.AddParameters(19, "@target_m9", string.IsNullOrEmpty(target_m9) ? "0" : target_m9);
                dbManager.AddParameters(20, "@target_m10", string.IsNullOrEmpty(target_m10) ? "0" : target_m10);
                dbManager.AddParameters(21, "@target_m11", string.IsNullOrEmpty(target_m11) ? "0" : target_m11);
                dbManager.AddParameters(22, "@target_m12", string.IsNullOrEmpty(target_m12) ? "0" : target_m12);
                dbManager.AddParameters(23, "@mtef_1", string.IsNullOrEmpty(mtef_1) ? "0" : mtef_1);
                dbManager.AddParameters(24, "@mtef_2", string.IsNullOrEmpty(mtef_2) ? "0" : mtef_2);
                dbManager.AddParameters(25, "@mtef_3", string.IsNullOrEmpty(mtef_3) ? "0" : mtef_3);
                dbManager.AddParameters(26, "@updated_by", UpdateBy);
                dbManager.AddParameters(27, "@updated_when", DateTime.Now);
                dbManager.AddParameters(28, "@sequence", Sequence);


                string strsql = "UPDATE bgm_request_kpi SET "
                                +" bgm_req_kpi_name=@bgm_req_kpi_name,"
                                + " bgm_req_kpi_descr=@bgm_req_kpi_descr,"
                                + " unit=@unit,"
                                + " bgm_req_kpi_weight=@bgm_req_kpi_weight,"
                                + " bgm_kpi_type_seq=@bgm_kpi_type_seq,"
                                + " score_1=@score_1,"
                                + " score_2=@score_2,"
                                + " score_3=@score_3,"
                                + " score_4=@score_4,"
                                + " score_5=@score_5,"
                                + " target_type=@target_type,"
                                + " target_m1=@target_m1,"
                                + " target_m2=@target_m2,"
                                + " target_m3=@target_m3,"
                                + " target_m4=@target_m4,"
                                + " target_m5=@target_m5,"
                                + " target_m6=@target_m6,"
                                + " target_m7=@target_m7,"
                                + " target_m8=@target_m8,"
                                + " target_m9=@target_m9,"
                                + " target_m10=@target_m10,"
                                + " target_m11=@target_m11,"
                                + " target_m12=@target_m12,"
                                + " mtef_1=@mtef_1,"
                                + " mtef_2=@mtef_2,"
                                + " mtef_3=@mtef_3,"
                                + " updated_by=@updated_by,"
                                + " updated_when=@updated_when WHERE bgm_req_kpi_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectActivityPlan(string Sequence, string descr, int act_month1, int act_month2, int act_month3, int act_month4, int act_month5, int act_month6, int act_month7, int act_month8, int act_month9, int act_month10, int act_month11, int act_month12, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@descr", descr); //bgm_req_date
                dbManager.AddParameters(1, "@act_month1", act_month1);
                dbManager.AddParameters(2, "@act_month2", act_month2);

                dbManager.AddParameters(3, "@act_month3", act_month3);
                dbManager.AddParameters(4, "@act_month4", act_month4);
                dbManager.AddParameters(5, "@act_month5", act_month5);
                dbManager.AddParameters(6, "@act_month6", act_month6);
                dbManager.AddParameters(7, "@act_month7", act_month7);
                dbManager.AddParameters(8, "@act_month8", act_month8);
                dbManager.AddParameters(9, "@act_month9", act_month9);
                dbManager.AddParameters(10, "@act_month10", act_month10);
                dbManager.AddParameters(11, "@act_month11", act_month11);
                dbManager.AddParameters(12, "@act_month12", act_month12);
                dbManager.AddParameters(13, "@updated_by", UpdateBy);
                dbManager.AddParameters(14, "@updated_when", DateTime.Now);
                dbManager.AddParameters(15, "@sequence", Sequence);


                string strsql = "UPDATE bgm_request_activity_plan SET "
                                + " descr=@descr,"
                                + " act_month1=@act_month1,"
                                + " act_month2=@act_month2,"
                                + " act_month3=@act_month3,"
                                + " act_month4=@act_month4,"
                                + " act_month5=@act_month5,"
                                + " act_month6=@act_month6,"
                                + " act_month7=@act_month7,"
                                + " act_month8=@act_month8,"
                                + " act_month9=@act_month9,"
                                + " act_month10=@act_month10,"
                                + " act_month11=@act_month11,"
                                + " act_month12=@act_month12,"
                                + " updated_by=@updated_by,"
                                + " updated_when=@updated_when WHERE bgm_req_act_plan_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectActivityPlanTarget(string Sequence, string target, string start_month, string end_month, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@target", target);
                dbManager.AddParameters(2, "@start_month", start_month);
                dbManager.AddParameters(3, "@end_month", end_month);
                dbManager.AddParameters(4, "@updated_by", UpdateBy);
                dbManager.AddParameters(5, "@updated_when", DateTime.Now);


                string strsql = "UPDATE bgm_request_activity_plan_target SET target=@target, start_month=@start_month, end_month=@end_month, updated_by=@updated_by, updated_when=@updated_when WHERE bgm_req_act_plan_target_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectExpense(string Sequence, string descr, string UpdateBy)
        {
            try
            {
                string strSQL = "";
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@descr", descr);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                strSQL = "UPDATE BGM_REQUEST_EXPENSE SET descr=@descr, updated_by=@updated_by, updated_when=@updated_when WHERE bgm_req_exp_seq = @sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectExpense(string Sequence, string bgm_req_seq, string bgm_source_seq, string bgm_exp_seq, string total_amount, string descr, Nullable<int> icm_res_item_seq, Nullable<int> unit_qty, Nullable<decimal> unit_price, Nullable<decimal> mtef1, Nullable<decimal> mtef2, Nullable<decimal> mtef3, string UpdateBy)
        {
            if (bgm_req_seq == null)
            {
                UpdateProjectExpense(Sequence, descr, UpdateBy);
                return;
            }
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(14);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@bgm_req_seq", bgm_req_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(4, "@descr", descr);
                dbManager.AddParameters(5, "@total_amount", total_amount);
                dbManager.AddParameters(6, "@icm_res_item_seq", icm_res_item_seq);
                dbManager.AddParameters(7, "@unit_qty", unit_qty);
                dbManager.AddParameters(8, "@unit_price", unit_price);
                dbManager.AddParameters(9, "@mtef1", mtef1);
                dbManager.AddParameters(10, "@mtef2", mtef2);
                dbManager.AddParameters(11, "@mtef3", mtef3);
                dbManager.AddParameters(12, "@updated_by", UpdateBy);
                dbManager.AddParameters(13, "@updated_when", DateTime.Now);


                int i;
                string strSQL;
                if (icm_res_item_seq != null)
                {
                    i = 0;
                }
                else
                {
                    strSQL = "select count(*)"
                        + " from bgm_request_expense"
                        + " where bgm_exp_seq = @bgm_exp_seq"
                        + " and bgm_req_seq = @bgm_req_seq"
                        + " and bgm_source_seq = @bgm_source_seq";
                    DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                    i = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }

                if (i > 0)
                {
                    strSQL = "UPDATE BGM_REQUEST_EXPENSE SET total_amount=@total_amount, descr=@descr, icm_res_item_seq=@icm_res_item_seq, unit_qty=@unit_qty, unit_price=@unit_price, mtef1=@mtef1, mtef2=@mtef2, mtef3=@mtef3, updated_by=@updated_by, updated_when=@updated_when WHERE bgm_req_exp_seq = @sequence";
                }
                else
                {
                    strSQL = "INSERT INTO BGM_REQUEST_EXPENSE(BGM_REQ_SEQ, BGM_SOURCE_SEQ, BGM_EXP_SEQ, TOTAL_AMOUNT, DESCR, ICM_RES_ITEM_SEQ, UNIT_QTY, UNIT_PRICE, MTEF1, MTEF2, MTEF3, CREATED_BY, CREATED_WHEN)"
                        + " VALUES(@bgm_req_seq, @bgm_source_seq, @bgm_exp_seq, @total_amount, @descr, @icm_res_item_seq, @unit_qty, @unit_price, @mtef1, @mtef2, @mtef3, @updated_by, @updated_when)";
                }

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateTotalAmountProjectExpense(string Sequence, string Name, string used_amount
            , string cal_amount, string rpt_type, string CreateBy)
        {
            //try
            //{
            //    dbManager.Open();
            //    dbManager.BeginTransaction();
            //    dbManager.CreateParameters(4);
            //    dbManager.AddParameters(0, "@sequence", Sequence);
            //    dbManager.AddParameters(1, "@total_amount", total_amount);
            //    dbManager.AddParameters(2, "@updated_by", UpdateBy);
            //    dbManager.AddParameters(3, "@updated_when", DateTime.Now);

            //    strSQL = "UPDATE BGM_PROJECT_EXPENSE"
            //        + " SET total_amount=@total_amount"
            //            + ", updated_by=@updated_by"
            //            + ", updated_when=@updated_when"
            //            + " WHERE bgm_proj_exp_seq = @sequence";

            //    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            //    dbManager.CommitTransaction();
            //}
            //catch
            //{
            //    dbManager.RollBackTransaction();
            //}
            //finally
            //{
            //    dbManager.Dispose();
            //}
        }

        public void UpdateProjectExpensePlan(string Sequence, string amount_month1, string amount_month2, string amount_month3, string amount_month4, string amount_month5, string amount_month6, string amount_month7, string amount_month8, string amount_month9, string amount_month10, string amount_month11, string amount_month12, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@amount_month1", amount_month1);
                dbManager.AddParameters(1, "@amount_month2", amount_month2);
                dbManager.AddParameters(2, "@amount_month3", amount_month3);
                dbManager.AddParameters(3, "@amount_month4", amount_month4);
                dbManager.AddParameters(4, "@amount_month5", amount_month5);
                dbManager.AddParameters(5, "@amount_month6", amount_month6);
                dbManager.AddParameters(6, "@amount_month7", amount_month7);
                dbManager.AddParameters(7, "@amount_month8", amount_month8);
                dbManager.AddParameters(8, "@amount_month9", amount_month9);
                dbManager.AddParameters(9, "@amount_month10", amount_month10);
                dbManager.AddParameters(10, "@amount_month11", amount_month11);
                dbManager.AddParameters(11, "@amount_month12", amount_month12);
                dbManager.AddParameters(12, "@updated_by", UpdateBy);
                dbManager.AddParameters(13, "@updated_when", DateTime.Now);
                dbManager.AddParameters(14, "@sequence", Sequence);


                string strsql = "UPDATE bgm_request_expense_plan SET "
                                + " amount_month1=@amount_month1,"
                                + " amount_month2=@amount_month2,"
                                + " amount_month3=@amount_month3,"
                                + " amount_month4=@amount_month4,"
                                + " amount_month5=@amount_month5,"
                                + " amount_month6=@amount_month6,"
                                + " amount_month7=@amount_month7,"
                                + " amount_month8=@amount_month8,"
                                + " amount_month9=@amount_month9,"
                                + " amount_month10=@amount_month10,"
                                + " amount_month11=@amount_month11,"
                                + " amount_month12=@amount_month12,"
                                + " updated_by=@updated_by,"
                                + " updated_when=@updated_when WHERE bgm_req_exp_plan_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectCustoms(string Sequence, string descr)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@descr", descr);


                string strsql = "UPDATE bgm_request_customs_field SET descr=@descr WHERE bgm_req_cus_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteProject(string Seq, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@Seq", Seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);
                string strsql = "UPDATE bgm_request"
                    + " SET record_status=@record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bgm_req_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectOption(string Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_request_option WHERE bgm_req_opt_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectKPI(string Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_request_kpi WHERE bgm_req_kpi_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectActivityPlan(string Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_request_activity_plan WHERE bgm_req_act_plan_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectActivityPlanTarget(string Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_request_activity_plan_target WHERE bgm_req_act_plan_target_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectExpense(string Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_request_expense WHERE bgm_req_exp_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void InsertProjectManager(string bgm_req_seq, string emp_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@bgm_req_seq", bgm_req_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                string strsql = "Insert into bgm_request_manager ("
                                            + "bgm_req_seq,"
                                            + "emp_seq)"
                                            + " values("
                                            + "@bgm_req_seq,"
                                            + "@emp_seq)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteProjectManager(string bgm_proj_mng_seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", bgm_proj_mng_seq);
                string strsql = "DELETE FROM bgm_request_manager WHERE bgm_req_mng_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ApproveProject(string bgm_req_seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", bgm_req_seq);
                string strsql = "UPDATE bgm_request set record_status = 'A' WHERE bgm_req_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ApproveProject(string bgm_req_seq, string status)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@Seq", bgm_req_seq);
                dbManager.AddParameters(1, "@status", status);
                string strsql = "UPDATE bgm_request set record_status = @status WHERE bgm_req_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectManager(string bgm_req_seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_req_seq", bgm_req_seq);

                string sql = "";
                // "select r.bgm_req_mng_seq as bgm_proj_mng_seq"//, rr.record_status"
                //            + ", concat(np.prefix_name, ' ' ,e.first_name, ' ', e.last_name) as name"
                //            + ", p.pos_ref, p.pos_name, a.tel, a.mobile, e.emp_seq"
                //            + " from bgm_request_manager r"
                //                + " inner join hrm_ps_employees e on r.emp_seq = e.emp_seq"
                //                + " inner join mas_name_prefixes np on np.name_prefix_seq = e.name_prefix_seq"
                //                + " left join hrm_mp_positions p on e.pos_seq = p.pos_seq"
                //                + " left join hrm_ps_emp_address a on e.emp_seq = a.emp_seq "
                //                + " and a.address_type_seq = 1"
                //                //+ " left join bgm_request rr on r.bgm_req_seq = rr.bgm_req_seq"
                //            + " where r.bgm_req_seq = @bgm_req_seq";

                sql = @"
select r.bgm_req_mng_seq as bgm_proj_mng_seq, 
concat(np.prefix_name, ' ' ,e.first_name, ' ', e.last_name) as name, 
p.pos_ref, p.pos_name, max(a.tel) as tel, max(a.mobile) as mobile, e.emp_seq 
from bgm_request_manager r inner join hrm_ps_employees e on r.emp_seq = e.emp_seq 
inner join mas_name_prefixes np on np.name_prefix_seq = e.name_prefix_seq 
left join hrm_mp_positions p on e.pos_seq = p.pos_seq 
left join hrm_ps_emp_address a on e.emp_seq = a.emp_seq  and a.address_type_seq = 1 
where r.bgm_req_seq = @bgm_req_seq 
group by r.bgm_req_mng_seq,  np.prefix_name,e.first_name,e.last_name,p.pos_ref, p.pos_name, e.emp_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public bool chkDubProjectOption(string bgm_req_seq, string bgm_option_seq)
        {
            try
            {
                string sql = "select *"
                           + " from bgm_request_option"
                           + " where bgm_req_seq =" + bgm_req_seq + " and bgm_option_seq =" + bgm_option_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                if (dt.Rows.Count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectControlByJobSite(string job_site_seq, string HeaderID)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable GetDataProjectByYearAndJobSite(string year, string job_site_seq, string emp_seq, string status)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);   // prj
                dbManager.AddParameters(1, "@Year", year);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(3, "@emp_seq", emp_seq);
                dbManager.AddParameters(4, "@status", status);

                string strSQL = "select r.bgm_req_seq as Sequence, r.bgm_req_code as bgm_prj_code"
                    + ", r.bgm_req_name as bgm_prj_name"
                    + ", r.date_start, r.date_end"
                    + ", re.total_amount"
                    + ", r.record_status, rs.record_status_name"
                    + " from bgm_request r"
                    + " left join (select bgm_req_seq"
                        + ", sum(coalesce(total_amount, 0)) as total_amount"
                        + " from bgm_request_expense"
                        + " group by bgm_req_seq"
                        + ") re on r.bgm_req_seq = re.bgm_req_seq"
                    + " left join sys_record_status rs on r.record_status = rs.record_status"

                    + (emp_seq.Length > 0 ? " inner join bgm_request_manager m on r.bgm_req_seq = m.bgm_req_seq"
                        + " and m.emp_seq = @emp_seq" : "")

                    + " where 0 = 0"
                    + (status.Length > 0 ? " and r.record_status = @status" : " and r.record_status not like @RecordStatus")
                    + (job_site_seq.Length > 0 ? " and (r.job_site_seq = @job_site_seq"
                        + " or r.job_site_seq_main = @job_site_seq"
                        + " or exists (select rp.* from bgm_request_pass rp"
                            + " where rp.bgm_req_seq = r.bgm_req_seq"
                            + " and rp.job_site_seq = @job_site_seq))" : "")
                    + (year.Length > 0 ? " and r.bgm_year = @Year" : "");
                    //+ (emp_seq.Length > 0 ? " and exist " : "");

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectByYearAndJobSiteAndSubAct(string year, string job_site_seq, string sub_act_seq, string status)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);   // prj
                dbManager.AddParameters(1, "@Year", year);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(3, "@sub_act_seq", sub_act_seq);
                dbManager.AddParameters(4, "@status", status);
                string sql = "select r.bgm_req_seq as Sequence, r.bgm_req_code as bgm_prj_code"
                                + ", r.bgm_req_name as bgm_prj_name"
                                + ", re.total_amount"
                                + ", s.bgm_strat_list_name as Strategy"
                                + ", s.bgm_strat_list_name , j.job_site_name"
                                + ", r.prj_type_seq, t.prj_type_name"
                            + " from bgm_request r"
                                + " left join bgm_strategy_data_list s on r.bgm_strat_list_seq = s.bgm_strat_list_seq"
                                + " left join bgm_mas_project_type t on r.prj_type_seq = t.prj_type_seq"
                                + " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
                                + " left join (select bgm_req_seq"
                                    + ", sum(coalesce(total_amount, 0)) as total_amount"
                                    + " from bgm_request_expense"
                                    + " group by bgm_req_seq"
                                    + ") re on r.bgm_req_seq = re.bgm_req_seq"
                            + " where 0 = 0"
                            + (status.Length > 0 ? (status == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = @status")
                                : " and r.record_status not like @RecordStatus")
                            //+ (status.Length > 0 ? " and r.record_status = @status" : " and r.record_status not like @RecordStatus")
                            + (year.Length > 0 ? " and r.bgm_year = @Year" : "")
                            + (job_site_seq.Length > 0 ? " and (r.job_site_seq = @job_site_seq"
                                + " or r.job_site_seq_main = @job_site_seq"
                                + " or exists (select rp.* from bgm_request_pass rp"
                                    + " where rp.bgm_req_seq = r.bgm_req_seq"
                                    + " and rp.job_site_seq = @job_site_seq))" : "")
                            + (sub_act_seq.Length > 0 ? " and r.bgm_strat_list_seq = @sub_act_seq" : "")
                            + " order by bgm_req_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectControlBySequenceForGridIn(string seq, string job_site_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        

        public DataTable GetReport_01(string year)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ApproveRecordStatus);


                string strsql = "select upper_bgm_strat_list_seq, upper_bgm_strat_list_name"
                        + ", bgm_strat_list_seq, bgm_strat_list_name, job_site_name_short as remark, duration"
                        + ", bgm_req_seq as bgm_proj_seq, bgm_req_code as bgm_proj_code, bgm_req_name as bgm_proj_name"
                        + ", coalesce(sum(TotalAmountSource1), 0) as TotalAmountSource1, coalesce(sum(TotalAmountSource6), 0) as TotalAmountSource6"
                        + ", coalesce(sum(TotalAmountSource7), 0) as TotalAmountSource7, coalesce(sum(TotalAmountSourceEtc), 0) as TotalAmountSourceEtc"
                        + " from ("
                            + "select ds2.bgm_strat_list_seq as upper_bgm_strat_list_seq, ds2.bgm_strat_list_name as upper_bgm_strat_list_name"
                            + ", ds1.bgm_strat_list_seq, ds1.bgm_strat_list_name, js.job_site_name_short"
                            + ", r.bgm_req_seq, r.bgm_req_code, r.bgm_req_name"
                            + ", '' as duration"
                            + ", case when e.bgm_source_seq = 1 then coalesce(sum(e.total_amount), 0) end as TotalAmountSource1"
                            + ", case when e.bgm_source_seq = 6 then coalesce(sum(e.total_amount), 0) end as TotalAmountSource6"
                            + ", case when e.bgm_source_seq = 7 then coalesce(sum(e.total_amount), 0) end as TotalAmountSource7"
                            + ", case when e.bgm_source_seq not like 1 and e.bgm_source_seq not like 6 and e.bgm_source_seq not like 7 then coalesce(sum(e.total_amount), 0) end as TotalAmountSourceEtc"
                        + " from  bgm_request r"
                            + " inner join bgm_request_expense e on e.bgm_req_seq = r.bgm_req_seq"
                            + " inner join bgm_mas_source s on s.bgm_source_seq = e.bgm_source_seq"
                            + " inner join bgm_strategy_data_mapping mp1 on mp1.bgm_strat_list_seq = r.bgm_strat_list_seq"
                            + " inner join bgm_strategy_data_list ds1 on ds1.bgm_strat_list_seq = mp1.bgm_strat_list_seq"
                            + " inner join bgm_strategy_data_mapping mp2 on mp2.bgm_strat_data_map_seq = mp1.upper_bgm_strat_data_map_seq"
                            + " inner join bgm_strategy_data_list ds2 on ds2.bgm_strat_list_seq = mp2.bgm_strat_list_seq"
                            + " inner join hrm_mp_job_sites js on js.job_site_seq = r.job_site_seq"
                        + " where r.bgm_year = @year"
                            + " and r.record_status = @RecordStatus"
                        + " group by ds2.bgm_strat_list_seq, ds2.bgm_strat_list_name, ds1.bgm_strat_list_seq, ds1.bgm_strat_list_name, js.job_site_name_short, s.bgm_source_seq, r.bgm_req_seq, r.bgm_req_code, r.bgm_req_name"
                    + " ) t1"
                    + " group by upper_bgm_strat_list_seq, upper_bgm_strat_list_name, bgm_strat_list_seq, bgm_strat_list_name, job_site_name_short, bgm_req_seq, bgm_req_code, bgm_req_name";

                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetReport_01_01(string year)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ApproveRecordStatus);

                string strsql = "select r.bgm_req_seq as bgm_proj_seq, mo2.bgm_option_seq as upper_bgm_option_seq, mo2.bgm_option_name as upper_bgm_option_name"
                        + ", mo1.bgm_option_seq, mo1.bgm_option_name"
                        + " from bgm_request r"
                            + " inner join bgm_request_option o on r.bgm_req_seq = o.bgm_req_seq"
                            + " inner join bgm_mas_option mo1 on mo1.bgm_option_seq = o.bgm_option_seq"
                            + " inner join bgm_mas_option mo2 on mo2.bgm_option_seq = mo1.upper_bgm_option_seq"
                        + " where r.bgm_year = @year"
                            + " and mo1.bgm_option_type_seq = 2";

                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetReport_01_02(string year)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ApproveRecordStatus);

                string strsql = "select r.bgm_req_seq as bgm_proj_seq, t.bgm_kpi_type_seq, t.bgm_kpi_type_name"
                        + ", k.bgm_req_kpi_seq as bgm_proj_kpi_seq, k.bgm_req_kpi_name as bgm_proj_kpi_name, k.unit"
                        + ", coalesce(target_m1, 0) + coalesce(target_m2, 0) + coalesce(target_m3, 0) + coalesce(target_m4, 0)"
                        + "+ coalesce(target_m5, 0) + coalesce(target_m6, 0) + coalesce(target_m7, 0) + coalesce(target_m8, 0)"
                        + "+ coalesce(target_m9, 0) + coalesce(target_m10, 0) + coalesce(target_m11, 0) + coalesce(target_m12, 0) as target"
                        + ", k.bgm_req_kpi_weight as weight"
                        + " from bgm_request r"
                            + " inner join bgm_request_kpi k on r.bgm_req_seq = k.bgm_req_seq"
                            + " inner join bgm_mas_kpi_type t on k.bgm_kpi_type_seq = t.bgm_kpi_type_seq"
                        + " where r.bgm_year = @year";

                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetReport_02(string year)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ApproveRecordStatus);

                string strsql = "select j.job_site_seq, j.job_site_ref, j.job_site_name"
                    + ", sl.bgm_strat_list_seq, sl.bgm_strat_list_code, sl.bgm_strat_list_name"
                    + ", s.bgm_source_seq, s.bgm_source_name"
                    + ", e2.bgm_exp_seq, e2.bgm_exp_code, e2.bgm_exp_name"
                    + ", sum(coalesce(re.total_amount, 0)) as total_amount"

                    + " from bgm_request r"
                    + " inner join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq"
                    + " inner join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
                    + " inner join bgm_strategy_data_list sl on r.bgm_strat_list_seq = sl.bgm_strat_list_seq"
                    + " inner join bgm_mas_source s on re.bgm_source_seq = s.bgm_source_seq"
                    + " inner join bgm_mas_expense e1 on re.bgm_exp_seq = e1.bgm_exp_seq"
                    + " left join bgm_mas_expense e2 on e1.upper_bgm_exp_seq = e2.bgm_exp_seq"

                    + " where r.record_status = @RecordStatus"
                    + " and r.bgm_year = @year"

                    + " group by j.job_site_seq, j.job_site_ref, j.job_site_name"
                    + ", sl.bgm_strat_list_seq, sl.bgm_strat_list_code, sl.bgm_strat_list_name"
                    + ", s.bgm_source_seq, s.bgm_source_name"
                    + ", e2.bgm_exp_seq, e2.bgm_exp_code, e2.bgm_exp_name";

                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetReport_04(string year)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ApproveRecordStatus);


                string strsql = "select upper_bgm_strat_list_seq, upper_bgm_strat_list_name"
                        + ", bgm_strat_list_seq, bgm_strat_list_name, ProjectCount"
                        + ", coalesce(sum(TotalAmountSource1), 0) as TotalAmountSource1, coalesce(sum(TotalAmountSource6), 0) as TotalAmountSource6"
                        + ", coalesce(sum(TotalAmountSource7), 0) as TotalAmountSource7, coalesce(sum(TotalAmountSourceEtc), 0) as TotalAmountSourceEtc"
                        + " from ("
                            + "select ds2.bgm_strat_list_seq as upper_bgm_strat_list_seq, ds2.bgm_strat_list_name as upper_bgm_strat_list_name"
                            + ", ds1.bgm_strat_list_seq, ds1.bgm_strat_list_name, (select count(*) from bgm_request s where s.bgm_strat_list_seq = r.bgm_strat_list_seq and s.record_status = 'A' ) as ProjectCount"
                            + ", s.bgm_source_seq, s.bgm_source_name"
                            + ", case when s.bgm_source_seq = 1 then coalesce(sum(e.total_amount), 0) end as TotalAmountSource1"
                            + ", case when s.bgm_source_seq = 6 then coalesce(sum(e.total_amount), 0) end as TotalAmountSource6"
                            + ", case when s.bgm_source_seq = 7 then coalesce(sum(e.total_amount), 0) end as TotalAmountSource7"
                            + ", case when s.bgm_source_seq not like 1 and s.bgm_source_seq not like 6 and s.bgm_source_seq not like 7 then coalesce(sum(e.total_amount), 0) end as TotalAmountSourceEtc"
                        + " from  bgm_request r"
                            + " inner join bgm_request_expense e on e.bgm_req_seq = r.bgm_req_seq"
                            + " inner join bgm_mas_source s on s.bgm_source_seq = e.bgm_source_seq"
                            + " inner join bgm_strategy_data_mapping mp1 on mp1.bgm_strat_list_seq = r.bgm_strat_list_seq"
                            + " inner join bgm_strategy_data_list ds1 on ds1.bgm_strat_list_seq = mp1.bgm_strat_list_seq"
                            + " inner join bgm_strategy_data_mapping mp2 on mp2.bgm_strat_data_map_seq = mp1.upper_bgm_strat_data_map_seq"
                            + " inner join bgm_strategy_data_list ds2 on ds2.bgm_strat_list_seq = mp2.bgm_strat_list_seq"
                        + " where r.bgm_year = @year"
                            + " and r.record_status = @RecordStatus"
                        + " group by ds2.bgm_strat_list_seq, ds2.bgm_strat_list_name, ds1.bgm_strat_list_seq, ds1.bgm_strat_list_name, s.bgm_source_seq, s.bgm_source_name"
                    + " ) t1"
                    + " group by upper_bgm_strat_list_seq, upper_bgm_strat_list_name, bgm_strat_list_seq, bgm_strat_list_name, ProjectCount";

                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetReport_05(string year, string type)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@type", type);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._ApproveRecordStatus);


                string strsql = "select upper_bgm_option_seq, upper_bgm_option_name"
                        + ", bgm_option_seq, bgm_option_name, ProjectCount"
                        + ", coalesce(sum(TotalAmountSource1), 0) as TotalAmountSource1, coalesce(sum(TotalAmountSource6), 0) as TotalAmountSource6"
                        + ", coalesce(sum(TotalAmountSource7), 0) as TotalAmountSource7, coalesce(sum(TotalAmountSourceEtc), 0) as TotalAmountSourceEtc"
                        + " from("
                            + "select  parent_seq as upper_bgm_option_seq, parent_name as upper_bgm_option_name, bgm_option_seq, bgm_option_name"
                            + ", count(bgm_req_seq) as ProjectCount, bgm_source_seq, bgm_source_name"
                            + ", case when bgm_source_seq = 1 then coalesce(sum(total_amount), 0) end as TotalAmountSource1"
                            + ", case when bgm_source_seq = 6 then coalesce(sum(total_amount), 0) end as TotalAmountSource6"
                            + ", case when bgm_source_seq = 7 then coalesce(sum(total_amount), 0) end as TotalAmountSource7"
                            + ", case when bgm_source_seq not like 1 and bgm_source_seq not like 6 and bgm_source_seq not like 7 then coalesce(sum(total_amount), 0) end as TotalAmountSourceEtc"
                        + " from ("
                            + " select mo.bgm_option_seq as parent_seq, mo.bgm_option_name as parent_name, o1.bgm_option_seq, o1.bgm_option_name, r.bgm_req_seq, r.bgm_req_code"
                                + ", bgm_req_name, s.bgm_source_seq, s.bgm_source_name, (o1.weight * coalesce(sum(e.total_amount), 0)) / 100 as total_amount"
                            + " from bgm_mas_option mo"
                            + " left join (	select o1.bgm_req_seq, o1.bgm_option_seq, mo.upper_bgm_option_seq, o1.weight, mo.bgm_option_name"
                                        + " from bgm_request_option o1 "
                                            + " left join bgm_mas_option mo on o1.bgm_option_seq = mo.bgm_option_seq "
                                        + " where mo.bgm_option_type_seq = @type"
                                        + " ) o1 on mo.bgm_option_seq = o1.upper_bgm_option_seq"
                            + " inner join bgm_request r  on o1.bgm_req_seq = r.bgm_req_seq"
                            + " inner join bgm_request_expense e on e.bgm_req_seq = r.bgm_req_seq"
                            + " inner join bgm_mas_source s on s.bgm_source_seq = e.bgm_source_seq"
                        + " where r.bgm_year = @year"
                            + " and  r.record_status = @RecordStatus"
                        + " group by mo.bgm_option_seq, mo.bgm_option_name, o1.bgm_option_seq, o1.bgm_option_name, r.bgm_req_seq, r.bgm_req_code, bgm_req_name, s.bgm_source_seq, s.bgm_source_name"
                        + " ) t"
                        + " group by  parent_seq, parent_name, bgm_option_seq, bgm_option_name, bgm_source_seq, bgm_source_name"
                    + " ) t1"
                    + " group by upper_bgm_option_seq, upper_bgm_option_name, bgm_option_seq, bgm_option_name, ProjectCount";

                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetReport_06(string year, string typeParent, string type)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@typeParent", typeParent);
                dbManager.AddParameters(2, "@type", type);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ApproveRecordStatus);

                string strsql = "select o2.bgm_option_seq as upper_bgm_option_seq, o2.bgm_option_name as upper_bgm_option_name, parent_seq as bgm_option_seq , parent_name as bgm_option_name"
                                + ", r.bgm_req_seq as bgm_proj_seq, r.bgm_req_code as bgm_proj_code, r.bgm_req_name as bgm_proj_name"
                                + ", (sum(ro.weight) * TotalAmountSource1) / 100 as TotalAmountSource1, (sum(ro.weight) * TotalAmountSource6) / 100 as TotalAmountSource6, (sum(ro.weight) * TotalAmountSource7) / 100 as TotalAmountSource7"
                            + " from ("
                                    + " select parent_seq, parent_name, bgm_req_seq, bgm_req_code, bgm_req_name"
                                        + ", coalesce(sum(TotalAmountSource1), 0) as TotalAmountSource1, coalesce(sum(TotalAmountSource6), 0) as TotalAmountSource6, coalesce(sum(TotalAmountSource7), 0) as TotalAmountSource7"
                                    + " from ("
                                                + " select mo.bgm_option_seq as parent_seq, mo.bgm_option_name as parent_name, r.bgm_req_seq, r.bgm_req_code, bgm_req_name"
                                                    + ", case when s.bgm_source_seq = 1 then (o1.weight * coalesce(sum(e.total_amount), 0)) / 100 end as TotalAmountSource1"
                                                    + ", case when s.bgm_source_seq = 6 then (o1.weight * coalesce(sum(e.total_amount), 0)) / 100 end as TotalAmountSource6"
                                                    + ", case when s.bgm_source_seq = 7 then (o1.weight * coalesce(sum(e.total_amount), 0)) / 100 end as TotalAmountSource7"
                                                + " from bgm_mas_option mo"
                                                    + " left join (	select o1.bgm_req_seq, o1.bgm_option_seq, mo.upper_bgm_option_seq, o1.weight, mo.bgm_option_name"
                                                                + " from bgm_request_option o1 "
                                                                    + " left join bgm_mas_option mo on o1.bgm_option_seq = mo.bgm_option_seq"
                                                                + " where mo.bgm_option_type_seq = @type"
                                                            + " ) o1 on mo.bgm_option_seq = o1.upper_bgm_option_seq"
                                                    + " inner join bgm_request r  on o1.bgm_req_seq = r.bgm_req_seq"
                                                    + " inner join bgm_request_expense e on e.bgm_req_seq = r.bgm_req_seq"
                                                    + " inner join bgm_mas_source s on s.bgm_source_seq = e.bgm_source_seq"
                                                + " where r.bgm_year = @year"
                                                    + " and r.record_status = @RecordStatus"
                                                + " group by mo.bgm_option_seq, mo.bgm_option_name, r.bgm_req_seq, r.bgm_req_code, bgm_req_name, s.bgm_source_seq, s.bgm_source_name"
                                            + " ) t"
                                    + " group by  parent_seq, parent_name, bgm_req_seq, bgm_req_code, bgm_req_name"
                                    + ") r"
                                + " inner join bgm_request_option ro  on r.bgm_req_seq = ro.bgm_req_seq"
                                + " inner join bgm_mas_option o1 on ro.bgm_option_seq = o1.bgm_option_seq "
                                + " inner join bgm_mas_option o2 on o1.upper_bgm_option_seq = o2.bgm_option_seq"
                            + " where o1.bgm_option_type_seq = @typeParent"
                            + " group by o2.bgm_option_seq, o2.bgm_option_name, r.bgm_req_seq, r.bgm_req_code, r.bgm_req_name, parent_seq, parent_name";

                dbManager.Open();
                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public void DeleteProjectOptionFromProjSeq(string BgmProjSeq, string BgmOptionTypeSeq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@Seq", BgmProjSeq);
                dbManager.AddParameters(1, "@Type", BgmOptionTypeSeq);
                string strsql = "DELETE FROM bgm_request_option"
                    + " WHERE bgm_req_seq = @Seq"
                    + " and bgm_option_seq in (select bgm_option_seq from bgm_mas_option where bgm_option_type_seq = @Type)";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListProjectOption(string bgm_proj_seq, string optionType)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@seq", bgm_proj_seq);
                dbManager.AddParameters(2, "@type", optionType);

                string sql = "select o.bgm_option_seq, o.bgm_option_name"
                            + ", re.weight, re.bgm_req_opt_seq as bgm_proj_opt_seq"
                        + " from bgm_request_option re"
                        + " left join bgm_mas_option o on re.bgm_option_seq = o.bgm_option_seq"
                        + " where o.bgm_option_type_seq = @type"
                        + " and bgm_req_seq = @seq"
                        + " order by o.bgm_option_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
